package com.siemens.industrial.web.mvc.dao.partner;

import com.siemens.industrial.model.partner.Metadata;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.sql.Types;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by qupen on 2016/8/31.
 */
@Service
public class PartnerMetadataDao {
    @Resource
    JdbcTemplate jdbcTemplate;

    private static final String databaseTag = "{database}";
    private static final String getMetadataQuery = "SELECT * FROM `" + databaseTag + "`.`metadata` WHERE `meta_id` = ?";
    private static final String getMetadataByKeyQuery = "SELECT * FROM `" + databaseTag + "`.`metadata` WHERE `meta_key` = ? AND `delete_time` IS NULL;";
    private static final String getMetadataListQuery = "SELECT * FROM `" + databaseTag + "`.`metadata` WHERE `delete_time` IS NULL;";
    private static final String saveMetadataQuery = "INSERT INTO `" + databaseTag + "`.`metadata` (`meta_key`, `meta_definition`) VALUES (?,?)";
    private static final String deleteMetadataQuery = "UPDATE `" + databaseTag + "`.`metadata` SET `delete_time` = now() WHERE `meta_id` = ?";
    private static final String getSettingQueey = "SELECT * FROM `" + databaseTag + "`.metadata WHERE meta_key='availability'";
    private static final String updateAvailabilityQuery = "UPDATE `" + databaseTag + "`.`metadata` SET `meta_definition` = ?  WHERE `meta_key` = 'availability'";
    private static final String getAllByKey = "SELECT * FROM `" + databaseTag + "`.`metadata` WHERE `meta_key`=?";
    private static final String recoverMetadata = "UPDATE `" + databaseTag + "`.`metadata` SET `meta_definition`=?,`delete_time`=NULL WHERE `meta_id`=?;";

    private static final String saveStatusTypes = "INSERT INTO `" + databaseTag + "`.`metadata` (`meta_id`, `meta_key`, `meta_definition`) VALUES ('2', 'statustype', ?);";
    private static final String getLastSettings = "SELECT max(start_time) start_time,end_time,value FROM `" + databaseTag + "`.state_info;";
    private static final String getSettingsToday = "SELECT * FROM `" + databaseTag + "`.state_info where start_time =date(now());";
    private static final String saveSettings = "INSERT INTO `"+databaseTag+"`.`state_info` (`type`, `value`) VALUES (?, ?);";
    private static final String updateSettings = "UPDATE `"+databaseTag+"`.`state_info` SET `value`= ? WHERE `type`=?;";
    private static final String findAllStateSettings = "SELECT * FROM `" + databaseTag + "`.state_info order by `type`;";
    private static final String deleteStatusType = "DELETE FROM `" + databaseTag + "`.`metadata` WHERE `meta_id`='2';";
    private static final String updateMetaData = "UPDATE `" + databaseTag + "`.`metadata` SET `meta_definition`=? WHERE `meta_id`=?;";

    private static final String findOneByMtType = "select * from `" + databaseTag + "`.`state_info` where `type`= ?;";
//    private static final String updateSettings = "UPDATE `" + databaseTag + "`.`state_info` SET `end_time`=?, `value`=? WHERE `start_time`=?;";

    public Metadata getMetadata(String database, String metaId) {
        try {
            return jdbcTemplate.queryForObject(getMetadataQuery.replace(databaseTag, database), new Object[]{metaId}, new int[]{Types.VARCHAR}, Metadata.getRowMapper());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public Map<String, Object> findLastSetting(String database) {
        try {
            return jdbcTemplate.queryForMap(getLastSettings.replace(databaseTag, database));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public Map<String, Object> findSettingsByNow(String database) {
        try {
            return jdbcTemplate.queryForMap(getSettingsToday.replace(databaseTag, database));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public Metadata getMetadataByKey(String database, String metaKey) {
        try {
            return jdbcTemplate.queryForObject(getMetadataByKeyQuery.replace(databaseTag, database), new Object[]{metaKey}, new int[]{Types.VARCHAR}, Metadata.getRowMapper());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public List<Metadata> getMetadataList(String database) {
        return jdbcTemplate.query(getMetadataListQuery.replace(databaseTag, database), Metadata.getRowMapper());
    }

    public void saveMetadata(String database, Metadata metadata) {
        jdbcTemplate.update(saveMetadataQuery.replace(databaseTag, database), pss -> {
                    pss.setString(1, metadata.getMetaKey());
                    pss.setString(2, metadata.getMetaDefinition());
                }
        );
    }

    public void deleteMetadata(String database, String metaId) {
        jdbcTemplate.update(deleteMetadataQuery.replace(databaseTag, database), pss -> {
                    pss.setString(1, metaId);
                }
        );
    }

    public List<Metadata> findAllByMetaKey(String database, String key) {
        return jdbcTemplate.query(getMetadataByKeyQuery.replace(databaseTag, database), new String[]{key}, Metadata.getRowMapper());
    }

    public Map<String, Object> getAvailabilitySetting(String database) {
        return jdbcTemplate.queryForMap(getSettingQueey.replace(databaseTag, database));
    }

    public void update(String database, String value) {
        jdbcTemplate.update(updateAvailabilityQuery.replace(databaseTag, database), value);
    }

    public List<Metadata> findAllByKey(String database, String key) {
        return jdbcTemplate.query(getAllByKey.replace(databaseTag, database), new String[]{key}, Metadata.getRowMapper());
    }

    @Transactional
    public void recover(String database, String metadataId, String definition) {
        jdbcTemplate.update(recoverMetadata.replace(databaseTag, database), definition, metadataId);
    }

//    @Transactional
//    public void setSttings(String database, Date startTime, Date endTIme, String value) {
//        jdbcTemplate.update(updateSettings.replace(databaseTag, database), endTIme, value, startTime);
//    }

    @Transactional
    public void saveSettings(String database,String type, String value) {
        jdbcTemplate.update(saveSettings.replace(databaseTag, database),type ,value);
    }

    @Transactional
    public void updateSettings(String database,String type, String value) {
        jdbcTemplate.update(updateSettings.replace(databaseTag, database),value ,type);
    }

    public List<Map<String, Object>> getAllStateSettings(String database) {
        return jdbcTemplate.queryForList(findAllStateSettings.replace(databaseTag, database));
    }

    public void saveStatusType(String database, String data) {
        jdbcTemplate.update(saveStatusTypes.replace(databaseTag, database), data);
    }

    public void deleteStatusType(String database) {
        jdbcTemplate.update(deleteStatusType.replace(databaseTag, database));
    }

    @Transactional
    public void updateById(String database, String metaDefinition, String metaId) {
        jdbcTemplate.update(updateMetaData.replace(databaseTag, database), metaDefinition, metaId);
    }

    public Map<String, Object> getOneByType(String tenantId, String type) {
        try {
            return jdbcTemplate.queryForMap(findOneByMtType.replace(databaseTag, tenantId), type);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }
}
